package weaver.tools.util;

import com.alibaba.fastjson.JSONObject;
import weaver.conn.RecordSet;
import weaver.general.Util;
import weaver.hrm.company.DepartmentComInfo;
import weaver.hrm.resource.ResourceComInfo;

import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Title: Ecology9
 * @Company: 泛微软件
 * @author: weaver.Dusongsong
 * @version: 1.0
 * @CreateDate: 2021-09-15
 * @Description: sql工具类
 * @ModifyLog:
 **/

public class SqlUtil {
    /**
     * @Description: 根据userid获取人员信息
     */
    public static Map<String, String> getUserInfo(String userid) {
        Map<String, String> map = new HashMap<>();
        try {
            ResourceComInfo resourceComInfo = new ResourceComInfo();
            DepartmentComInfo departmentComInfo = new DepartmentComInfo();
            map.put("workcode", resourceComInfo.getWorkcode(userid));
            map.put("lastname", resourceComInfo.getLastname(userid));
            map.put("loginid", resourceComInfo.getLoginID(userid));
            map.put("managerid", resourceComInfo.getManagerID(userid));
            map.put("subcompanyid", resourceComInfo.getSubCompanyID(userid));
            map.put("status", resourceComInfo.getStatus(userid));
            String departmentid = resourceComInfo.getDepartmentID(userid);
            map.put("departmentid", departmentid);
            map.put("departmentname", departmentComInfo.getDepartmentname(departmentid));
            map.put("departmentcode", departmentComInfo.getDepartmentCode(departmentid));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return map;
    }

    /**
     * @Description: 根据表单和字段获取下拉框值
     */
    public static String getSelectItem(int billid, String fieldname, String selectvalue) {
        RecordSet rs = new RecordSet();
        String sql = "select t1.selectvalue,t1.selectname from workflow_selectitem t1 left join workflow_billfield t2\n" +
                "on t1.fieldid=t2.id where abs(t2.billid)=? and t2.fieldname=? and t1.selectvalue=?";
        rs.executeQuery(sql, billid, fieldname, selectvalue);
        rs.next();
        String selectname = Util.null2String(rs.getString("selectname"));
        return selectname;
    }

    /**
     * @Description: 根据loginID获取客户模块客户姓名
     */
    public static String getCrmLoginId2Name(String loginid) {
        RecordSet rs = new RecordSet();
        String sql = "select name from crm_customerinfo where portalloginid=?";
        rs.executeQuery(sql, loginid);
        rs.next();
        String name = weaver.general.Util.null2String(rs.getString("name"));
        return name;
    }

    /**
     * @Description: 获取流程是否归档状态
     */
    public static String getCurrentNodeType(String workflowid, String requestid) {
        //0：创建 1：审批 2：提交 3：归档
        String curentNodeType = "";
        RecordSet rs = new RecordSet();
        rs.executeQuery("select currentnodetype from workflow_requestbase where workflowid=? and requestid=?", workflowid, requestid);
        if (rs.next()) {
            curentNodeType = Util.null2String(rs.getString(1));
        }
        return curentNodeType;
    }

    /**
     * @Description: 判断是不是工作日
     */
    public String isWorkDay(Date date) {
        RecordSet rs = new RecordSet();
        String isWorkDay = "";
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);//日期转成Calendar
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String strDate = sdf.format(date);
        if (calendar.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY || calendar.get(Calendar.DAY_OF_WEEK) == Calendar.SATURDAY) {
            isWorkDay = "0";//周六 周日
            rs.execute("select 1 from hrmpubholiday where holidaydate = '" + strDate + "' and changetype = 2");
            if (rs.next()) {
                isWorkDay = "1";
            }
        } else {
            isWorkDay = "1";
            rs.execute("select 1 from hrmpubholiday where holidaydate='" + strDate + "' and (changetype = 1 or changetype = 3)");
            if (rs.next()) {
                isWorkDay = "0";
            }
        }
        return isWorkDay;
    }

    /**
     * @Description: 判断两个日期之间的工作日时间
     */
    public int getWorkDays(String fromDate, String toDate) {
        int days = 0;
        try {
            String[] toDateArr = toDate.split("-");
            int toDateYear = Integer.parseInt(toDateArr[0]);
            int toDateMonth = Integer.parseInt(toDateArr[1]);
            int toDateDay = Integer.parseInt(toDateArr[2]);
            Calendar end = Calendar.getInstance();
            end.set(toDateYear, toDateMonth - 1, toDateDay);
            Long endTime = end.getTimeInMillis();

            String[] fromDateArr = fromDate.split("-");
            int fromDateYear = Integer.parseInt(fromDateArr[0]);
            int fromDateMonth = Integer.parseInt(fromDateArr[1]);
            int fromDateDay = Integer.parseInt(fromDateArr[2]);
            Calendar start = Calendar.getInstance();
            start.set(fromDateYear, fromDateMonth - 1, fromDateDay);
            Long startTime = start.getTimeInMillis();
            Long oneDay = 1000 * 60 * 60 * 24L;
            Long temp = startTime;
            RecordSet rs;
            while (temp <= endTime) {
                Date d = new Date(temp);
                rs = new RecordSet();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                String strDate = sdf.format(d);
                temp += oneDay;
                if ("1".equals(isWorkDay(sdf.parse(strDate)))) {
                    days++;
                }
            }
        } catch (Exception e) {
            // TODO: handle exception
            System.out.println(e.getMessage());
        }
        return days;
    }

    /**
     * @Description: 获取节点名称
     */
    public static JSONObject getNodebaseNodename(int nodeid) {
        RecordSet rs = new RecordSet();
        JSONObject json = new JSONObject();
        rs.executeQuery("select isstart,nodename,isend from workflow_nodebase where id=?", nodeid);
        rs.next();
        json.put("isstart", Util.null2String(rs.getString("isstart")));
        json.put("nodename", Util.null2String(rs.getString("nodename")));
        json.put("isend", Util.null2String(rs.getString("isend")));
        return json;
    }

    /**
     * @Description: 根据请求id获取表名
     */
    public static String getTableName(String requestid) {
        RecordSet rs = new RecordSet();
        rs.execute("select t3.tablename from workflow_requestbase t1 "
                + " left join workflow_base t2 on t1.workflowid = t2.id "
                + " left join workflow_bill t3 on t2.formid = t3.id "
                + " where t1.requestid = " + requestid);
        return rs.next() ? rs.getString("tablename") : "";
    }

    /**
     * @Description: 获取附件上传目录ID
     */
    public int getDoccategory(String formid) {
        RecordSet rs = new RecordSet();
        rs.executeQuery("select replace(doccategory,',','') from workflow_base where abs(formid)=?", formid);
        rs.next();
        return Util.getIntValue(rs.getString(1), -1);
    }

    /**
     * @Description: 数据中心获取指定版本任务数据
     */
    public static String getFormid(String appid) {
        RecordSet rs = new RecordSet();
        String formids = "";
        rs.executeQuery("select t.formid from edc_pagesheet t " +
                "left join edc_page t1 on t.pageid=t1.id where t1.appid=?", appid);
        while (rs.next()) {
            String formid = Util.null2String(rs.getString(1));
            formids += "".equals(formids) ? formid : "," + formid;
        }
        return formids;
    }

    /**
     * @Description: 获取数据表的明细表
     */
    public static String getMainTable(String formid) {
        RecordSet rs = new RecordSet();
        rs.executeQuery("select tablename from workflow_bill where id=?", formid);
        rs.next();
        return Util.null2String(rs.getString("tablename"));
    }

    /**
     * @Description: 获取数据表的明细表
     */
    public static List<Map<String, String>> getDetailTable(String formid) {
        RecordSet rs = new RecordSet();
        List<Map<String, String>> mapList = new ArrayList<>();
        rs.executeQuery("select orderid,tablename from Workflow_billdetailtable  where billid =? order by tablename", formid);
        while (rs.next()) {
            Map<String, String> dataMap = new HashMap<>();
            String orderid = Util.null2String(rs.getString("orderid"));
            String tablename = Util.null2String(rs.getString("tablename"));
            dataMap.put("orderid", orderid);
            dataMap.put("tablename", tablename);
            mapList.add(dataMap);
        }
        return mapList;
    }

    /**
     * @Description: 根据appid和版本号获取任务ID
     */
    public static String getTaskIdByVersion(String appid, String bbh) {
        RecordSet rs = new RecordSet();
        rs.executeQuery("select max(id) from edc_task where appid=? and bbh=? and isfinished=1", appid, bbh);
        rs.next();
        return Util.null2String(rs.getString(1));
    }

    /**
     * @Description: 获取任务节点ID
     */
    public static String getTasknodeId(String taskid) {
        RecordSet rs = new RecordSet();
        rs.executeQuery(" select max(id) from edc_tasknode where taskid=?", taskid);
        rs.next();
        return Util.null2String(rs.getString(1));
    }

    /**
     * @Description: 判断人员是否有打卡记录
     */
    public boolean isScheduleSign(String requestid, String currendate) {
        RecordSet rs = new RecordSet();
        rs.executeQuery("select count(id) from HrmScheduleSign where userId=? and signDate=?", requestid, currendate);
        rs.next();
        return Util.getIntValue(rs.getString(1)) > 0;
    }

}
